# 科目余额表应展开：其他应付款单位余额表、坏账准备、合同资产减值准备、合同结算、应交税费、财务费用
# 应该检查的科目：其他流动资产、其他流动负债、合同资产和合同负债（如存在已计量未完工）

from openpyxl import load_workbook
import json

from sys import path
orign_path = 'd:/python&work'
path.append(orign_path)
from little_function import convert

# def real_round(num_to_round, divisor=10000):
#     # 解决假四舍五入的问题
#     if divisor == 1:
#         return num_to_round
#     quotient, remainder = divmod(num_to_round, divisor)
#     if remainder >= divisor//2:
#         quotient += 1
#     return int(quotient)

def get_value(value):
    if value is None:
        raise ValueError("输入值不能为空。")
    if isinstance(value, float) or isinstance(value, int):
        return value
    elif isinstance(value, str):
        try:
            return float(value.replace(',', ''))
        except ValueError:
            raise ValueError(f"无法将输入值 '{value}' 转换为浮点数。")

def other_receivables_payables(source_data, d=10000):
    _other_receivables = source_data['1221']['H']['account']
    _other_payables_qd = source_data['Z01140560000000000']['H']['account']
    _other_payables_others = source_data['2241']['H']['account'] - _other_payables_qd
    if _other_receivables > _other_payables_qd:
        other_receivables = _other_receivables - _other_payables_qd
        other_payables = _other_payables_others
    else:
        other_receivables = 0
        other_payables = _other_payables_others + _other_payables_qd - _other_receivables
    return {'C20': convert.convert_to_wan(other_receivables, divisor=d), 'C74': convert.convert_to_wan(other_payables, divisor=d)}

xlsx_file = "d:/source.xlsx"
wb_source = load_workbook(xlsx_file, data_only=True)
ws_source = wb_source.active
numbers = [c.value for c in ws_source['A'][5:]]
f_acount = [get_value(c.value) for c in ws_source['F'][5:]]
symbol = [c.value for c in ws_source['G'][5:]]
h_acount = [get_value(c.value) for c in ws_source['H'][5:]]

source_data = {}    # 科目余额表中的数据
for i, num in enumerate(numbers):
    source_data[num] = {'F': {'symbol': '贷', 'account': f_acount[i]}, 'H': {'symbol': symbol[i], 'account': h_acount[i]}}

with open('D:/python&work/statement/rule.json', 'r', encoding='utf-8') as f:
    rules = json.load(f)
wb_statement = load_workbook('d:/statement.xlsx')
ws_statement  = wb_statement.active
# 删除 C5:C171 的数据
for i in range(5, 171):
    ws_statement[f'C{i}'] = ''

results = {}
for i, num in enumerate(numbers):
    try:
        info_rule = rules[num]
    except KeyError:
        # print(f'No rule for {num}')
        continue
    # 检查余额方向
    direction = info_rule['direction_check']
    if direction:
        if source_data[num]['H']['symbol'] not in {direction, '平'}:
            print(f'{num} direction error')
            continue
    calculate_rule = info_rule['calculate']
    account = 0
    for sign, calculate_num, col in calculate_rule:
        try:
            account += source_data[calculate_num][col]['account'] * (-1 if sign == '-' else 1)
        except KeyError as e:
            print(f'Error processing rule for {calculate_num}: {e}')
            break
        except TypeError as e:
            print(f'{e}: {calculate_num} {col} 的account 值有误')
            break
    # 检查结果正负
    if account < 0 and info_rule['result_negative'] == 'False':
        print(f'{num}-error（结果为负）')
        continue
    results[f'C{info_rule["location"]}'] = convert.convert_to_wan(account)     # 万元，月报
    # results[f'C{info_rule["location"]}'] = convert.convert_to_wan(account, divisor=1)     # 元，季报

other_rec_pay = other_receivables_payables(source_data)     # 万元，月报
# other_rec_pay = other_receivables_payables(source_data, d=1)     # 元，季报
results.update(other_rec_pay)
for key,value in results.items():
    ws_statement[key] = value
# for item in results.items():
#     ws_statement[item.key()] = item.value()
            
wb_statement.save('d:/statement.xlsx')
